-- SCRIPT TO BE ADAPTED TO EACH CLIENT
-- THIS SCRIPT CALCULATE(S) THE SCORES 


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION score_insurance1_f() RETURNS INTEGER AS $$

DECLARE
  v_j integer;
  v_b float[] := array[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
  v_hccurs refcursor;
  v_row read_score_insurance1_t%ROWTYPE;
  v_status integer;
  v_batch_run_date timestamp;
  v_age integer;
  v_cust_id integer;
  v_scursa refcursor;
  v_scursb refcursor;
  v_scursc refcursor;
  v_scursd refcursor;
  v_srec record;
  v_tot_commercial_count integer;
  v_tot_tpl_count       integer;
  v_tot_medicaid_count  integer;
  v_tot_medicare_count  integer;
  v_commercial_count    integer;
  v_tpl_count           integer;
  v_medicaid_count      integer;
  v_medicare_count      integer;
  v_override_commercial integer;
  v_override_tpl        integer;
  v_override_medicaid   integer;
  v_override_medicare   integer;
  v_group               varchar(15);

BEGIN


 ----------------------------------------------------------------------------------
  -- STEP 1: INITIALIZE THE FIELDS 
 ----------------------------------------------------------------------------------


  SELECT INTO v_batch_run_date,v_cust_id our_run_date,cust_id FROM batch_insurance1_t limit 1;




 ----------------------------------------------------------------------------------
  -- STEP 2: CALCULATE THE NUMBERS TO BE SENT TO commercial, tpl, medicaid and medicare 
  -- BASED ON THRESHOLD NUMBERS 
  ----------------------------------------------------------------------------------


  SELECT INTO v_tot_commercial_count sum( (case when emdeon_commercial_flag = '1' then 1 else 0 end) )::integer from batch_insurance1_t;
  SELECT INTO v_tot_tpl_count        sum( (case when emdeon_tpl_flag = '1'        then 1 else 0 end) )::integer from batch_insurance1_t;
  SELECT INTO v_tot_medicaid_count   sum( (case when emdeon_medicaid_flag = '1'   then 1 else 0 end) )::integer from batch_insurance1_t;
  SELECT INTO v_tot_medicare_count   sum( (case when emdeon_medicare_flag = '1'   then 1 else 0 end) )::integer from batch_insurance1_t;



  ----------------------------------------------------------------------------------
  -- STEP 3: DELETE ALL ROWS IN score_temp_insurance1_t TABLE CONTAINING THE SCORES FOR THE BATCH
  -- CREATE ALL TEMPORARY TABLES
  ----------------------------------------------------------------------------------

  TRUNCATE score_temp_insurance1_t;



  CREATE TEMP TABLE temp_commercial (
  hid                           varchar(6),
  acctnum			varchar(20),
  scoring_code_commercial	varchar(3)
  );

  CREATE TEMP TABLE temp_tpl (
  hid                           varchar(6),
  acctnum                       varchar(20),
  scoring_code_tpl              varchar(3)
  );

  CREATE TEMP TABLE temp_medicaid (
  hid                           varchar(6),
  acctnum                       varchar(20),
  scoring_code_medicaid         varchar(3)
  );

  CREATE TEMP TABLE temp_medicare (
  hid                           varchar(6),
  acctnum                       varchar(20),
  scoring_code_medicare         varchar(3)
  );




  ----------------------------------------------------------------------------------
  -- STEP 4: OBTAIN ALL THE FIELDS NEEDED FOR SCORING 
  -- v_hccurs ROW RECORD FORMAT SHOULD MATCH EXACTLY THE LAYOUT OF read_score_insurance1_t TABLE
  ----------------------------------------------------------------------------------

  RAISE NOTICE 'START SCORE CALCULATION AND INSERT INTO score_insurance1_temp_t';
  PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,4,'START SCORE CALCULATION AND INSERT INTO score_temp_insurance1_t');


  OPEN v_hccurs FOR SELECT
                        -- from batch_insurance1_t 
  			a.cust_id                  as cust_id,
            a.hid                      as hid,
            a.acctnum                  as acctnum,
  			a.lname                    as lname,
            a.service_date_begin       as service_date_begin,
  			a.dob                      as dob,
            a.ssn                       as ssn,
  			a.g_employ_name            as g_employ_name,
            (case when a.balance IS NULL and a.charges IS NOT NULL then a.charges
                  when a.balance IS NOT NULL                       then a.balance
                  else                                                  0
                  end)                 as balance,
            a.provider_state           as provider_state,
            a.commercial_score         as commercial_score,
            a.scoring_code_commercial  as scoring_code_commercial,
            a.tpl_score                as tpl_score,
            a.scoring_code_tpl         as scoring_code_tpl,
            a.medicaid_score           as medicaid_score,
            a.scoring_code_medicaid    as scoring_code_medicaid,
            a.medicare_score           as medicare_score,
            a.scoring_code_medicare    as scoring_code_medicare,
            a.emdeon_tpl_flag          as emdeon_tpl_flag,
            a.emdeon_commercial_flag   as emdeon_commercial_flag,
            a.emdeon_medicare_flag     as emdeon_medicare_flag,
            a.emdeon_medicaid_flag     as emdeon_medicaid_flag,
            a.threshold_bal_commercial as threshold_bal_commercial,
            a.threshold_bal_tpl        as threshold_bal_tpl,
            a.threshold_bal_medicaid   as threshold_bal_medicaid,
            a.threshold_bal_medicare   as threshold_bal_medicare,
            a.count_commercial_max     as count_commercial_max,
            a.count_tpl_max            as count_tpl_max,
            a.count_medicaid_max       as count_medicaid_max,
            a.count_medicare_max       as count_medicare_max,
            a.calc_group               as calc_group,
            a.processing_type          as processing_type,

            -- from zip4 table 
            b.gaca_oldest        as gaca_oldest,
            b.gaca_td            as gaca_td,
            b.gaca_pd            as gaca_pd,
            b.gaca_bop           as gaca_bop,
            b.gaca_bhcop         as gaca_bhcop,
            b.gaca_utilbop       as gaca_utilbop,
            b.gaca_pctsat        as gaca_pctsat,
            b.gaca_bd24m         as gaca_bd24m,
            b.gaca_mhcop         as gaca_mhcop,
            b.gaca_mop           as gaca_mop,

            -- from census
            a.pct_poverty        as pct_poverty,
            a.pct_hh_with_ssn    as pct_hh_with_ssn,
            a.pct_hh_with_public as pct_hh_with_public,
            a.pct_unemployed1    as pct_unemployed1,
            a.per_capita_income  as per_capita_income,
            a.median_hh_income   as medicaid_hh_income
 

            FROM
                         
                      (SELECT

                        batch.*,
                        (CASE WHEN gr.count_commercial_max <> 0 THEN floor(gr.count_commercial_max)::integer + 1
                              ELSE                                   0
                              END) as count_commercial_max,
                        (CASE WHEN gr.count_tpl_max <> 0 THEN floor(gr.count_tpl_max)::integer + 1
                              ELSE                                   0
                              END) as count_tpl_max,
                        (CASE WHEN gr.count_medicaid_max <> 0 THEN floor(gr.count_medicaid_max)::integer + 1
                              ELSE                                   0
                              END) as count_medicaid_max,
                        (CASE WHEN gr.count_medicare_max <> 0 THEN floor(gr.count_medicare_max)::integer + 1
                              ELSE                                   0
                              END) as count_medicare_max


                        FROM

                            (SELECT
                                 prod.*,
                                 census.pct_poverty,
                                 census.pct_hh_with_ssn,
                                 census.pct_hh_with_public,
                                 census.pct_unemployed1,
                                 census.per_capita_income,
                                 census.median_hh_income

                               FROM

                      	    		(SELECT
                                            *
                                         FROM
                                            batch_insurance1_t 

                                         WHERE (emdeon_tpl_flag = '1' OR emdeon_commercial_flag = '1' OR 
                                                emdeon_medicaid_flag = '1' OR emdeon_medicare_flag = '1') 
                                        ) as prod 

                                        LEFT JOIN

                                        (SELECT pct_poverty,
                                                pct_hh_with_ssn,
                                                pct_hh_with_public,
                                                pct_unemployed1,
                                                per_capita_income,
                                                median_hh_income,
                                                zipcode
                                         FROM ml_demographics_t
                                         ) as census 

                                         ON
                                         (prod.zip5 = census.zipcode)

                             ) as batch

                             LEFT JOIN
                              
                             (SELECT

                               calc_group ,
                               sum((case when emdeon_commercial_flag = '1' THEN threshold_commercial
                                         else                                   0
                                         end)) as count_commercial_max,
                               sum((case when emdeon_tpl_flag = '1'        THEN threshold_tpl
                                         else                                   0
                                         end)) as count_tpl_max,
                               sum((case when emdeon_medicaid_flag = '1'   THEN threshold_medicaid
                                         else                                   0
                                         end)) as count_medicaid_max,
                               sum((case when emdeon_medicare_flag = '1'   THEN threshold_medicare
                                         else                                   0
                                         end)) as count_medicare_max
                              FROM
                                batch_insurance1_t
                              GROUP BY
                                calc_group

                             ) as gr 

                            ON (batch.calc_group = gr.calc_group)

                      ) AS a


                      LEFT JOIN


                      (SELECT
                            *
                         FROM
                            ml_gaca_demographics_t
                       ) as b 

                      ON (a.zip5 = b.gaca_zip5 and a.zip4 = b.gaca_zip4);



  -----------------------------
  --  START SCORING LOOP
  ----------------------------

  v_status := 0;
                        
  LOOP
    FETCH v_hccurs INTO v_row;
    IF NOT FOUND THEN
      EXIT;
    END IF;
    v_status := v_status + 1;


    -------------------------------------------------------
    -- STEP 4.1  INITIALIZE THE ARRAY TO CALCULATE THE SCORE
    FOR v_j IN 1..17 LOOP
      v_b[v_j] := 0;
    END LOOP;


    --------------------------
    -- CENSUS VARIABLES
    IF (v_row.pct_poverty IS NULL) THEN v_row.pct_poverty := 9.9999;
    END IF;

    IF (v_row.pct_hh_with_ssn IS NULL) THEN v_row.pct_hh_with_ssn := 9.9999;
    END IF;

    IF (v_row.pct_hh_with_public IS NULL) THEN v_row.pct_hh_with_public := 9.9999;
    END IF;

    IF (v_row.pct_unemployed1 IS NULL) THEN v_row.pct_unemployed1 := 9.9999;
    END IF;

    IF (v_row.per_capita_income IS NULL) THEN v_row.per_capita_income := 999999;
    END IF;

    IF (v_row.median_hh_income IS NULL) THEN v_row.median_hh_income := 999999;
    END IF;


    --------------------------------------------------------
    -- STEP 4.2 TREAT NULL VALUES OF GACA DATA 
    IF (v_row.gaca_oldest IS NULL) THEN v_row.gaca_oldest := 9999;
    END IF;

    IF (v_row.gaca_td IS NULL) THEN v_row.gaca_td := 99;
    END IF;

    IF (v_row.gaca_pd IS NULL) THEN v_row.gaca_pd := 99;
    END IF;

    IF (v_row.gaca_bop IS NULL) THEN v_row.gaca_bop := 99;
    END IF;

    IF (v_row.gaca_bhcop IS NULL) THEN v_row.gaca_bhcop := 9999999;
    END IF;

    IF (v_row.gaca_utilbop IS NULL) THEN v_row.gaca_utilbop := 9.9999;
    END IF;

    IF (v_row.gaca_pctsat IS NULL) THEN v_row.gaca_pctsat := 9.9999;
    END IF;

    IF (v_row.gaca_bd24m IS NULL) THEN v_row.gaca_bd24m := 99;
    END IF;

    IF (v_row.gaca_mhcop IS NULL) THEN v_row.gaca_mhcop := 9999999;
    END IF;

    IF (v_row.gaca_mop IS NULL) THEN v_row.gaca_mop := 99;
    END IF;

    -- END OF STEP 4.2 


    -- STEP 4.3 IMPORTANT
    -- ASSIGN A START VALUES FOR THE SCORES CALCULATED

    v_row.commercial_score         := NULL;
    v_row.scoring_code_commercial  := NULL; 
    v_row.tpl_score                := NULL;
    v_row.scoring_code_tpl         := NULL;
    v_row.medicaid_score           := NULL;
    v_row.scoring_code_medicaid    := NULL;
    v_row.medicare_score           := NULL;
    v_row.scoring_code_medicare    := NULL;
    v_override_commercial          := 0;
    v_override_tpl                 := 0;
    v_override_medicaid            := 0;
    v_override_medicare            := 0;


    --
    -- STEP 4.4 CUSTOM ATTRIBUTES 

    IF v_row.dob IS NOT NULL and v_row.service_date_begin IS NOT NULL THEN v_age := ml_ddiff_f('yr',v_row.dob,v_row.service_date_begin)::integer;
    ELSE                                                                   v_age := 999;
    END IF;


    --
    -- STEP 4.5 SCORE CALCULATION

    IF v_row.emdeon_commercial_flag = '1' OR v_row.emdeon_tpl_flag = '1' THEN

        v_row.commercial_score         := 0; 
        v_row.scoring_code_commercial  := 'F';
        v_row.tpl_score                := 0;
        v_row.scoring_code_tpl         := 'F';

        IF v_row.processing_type <> 'T'             THEN
 
            -- SCORING ALGORITHM FOR COMMERCIAL;

            IF     (0    <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 1.32)                        THEN v_b[1] :=   0.116337;
            ELSIF  (1.32 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 2.44)                        THEN v_b[1] :=   0.044220;
            ELSIF  (2.44 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 4.03)                        THEN v_b[1] :=   0.016377;
            ELSIF  (4.03 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m <= 92 )                        THEN v_b[1] :=  -0.083332;
            ELSIF  (92   <  v_row.gaca_bd24m) AND (v_row.gaca_bd24m <= 99 )                        THEN v_b[1] :=  -0.075259;
            END IF;

            IF     (0    <= v_row.gaca_bop) AND (v_row.gaca_bop < 0.59)                            THEN v_b[2] :=  -0.078914;
            ELSIF  (0.59 <= v_row.gaca_bop) AND (v_row.gaca_bop < 1.00)                            THEN v_b[2] :=  -0.022531;
            ELSIF  (1.00 <= v_row.gaca_bop) AND (v_row.gaca_bop < 1.56)                            THEN v_b[2] :=   0.003650;
            ELSIF  (1.56 <= v_row.gaca_bop) AND (v_row.gaca_bop < 2.17)                            THEN v_b[2] :=   0.042379;
            ELSIF  (2.17 <= v_row.gaca_bop) AND (v_row.gaca_bop < 2.77)                            THEN v_b[2] :=   0.065506;
            ELSIF  (2.77 <= v_row.gaca_bop) AND (v_row.gaca_bop <= 92 )                            THEN v_b[2] :=   0.093016;
            ELSIF  (92   <  v_row.gaca_bop) AND (v_row.gaca_bop <= 99 )                            THEN v_b[2] :=  -0.040683;
            END IF;

            IF     (0    <= v_row.gaca_mop) AND (v_row.gaca_mop < 0.14)                            THEN v_b[3] :=  -0.064281;
            ELSIF  (0.14 <= v_row.gaca_mop) AND (v_row.gaca_mop < 0.33)                            THEN v_b[3] :=   0.007494;
            ELSIF  (0.33 <= v_row.gaca_mop) AND (v_row.gaca_mop < 0.57)                            THEN v_b[3] :=   0.062468;
            ELSIF  (0.57 <= v_row.gaca_mop) AND (v_row.gaca_mop <= 92 )                            THEN v_b[3] :=   0.106051;
            ELSIF  (92   <  v_row.gaca_mop) AND (v_row.gaca_mop <= 99 )                            THEN v_b[3] :=  -0.052862;
            END IF;

            IF     (0     <= v_row.gaca_td) AND (v_row.gaca_td < 5.70 )                            THEN v_b[4] :=  -0.230748;
            ELSIF  (5.70  <= v_row.gaca_td) AND (v_row.gaca_td < 9.23 )                            THEN v_b[4] :=  -0.083303;
            ELSIF  (9.23  <= v_row.gaca_td) AND (v_row.gaca_td < 11.71)                            THEN v_b[4] :=  -0.026670;
            ELSIF  (11.71 <= v_row.gaca_td) AND (v_row.gaca_td < 14.71)                            THEN v_b[4] :=   0.099087;
            ELSIF  (14.71 <= v_row.gaca_td) AND (v_row.gaca_td <= 92  )                            THEN v_b[4] :=   0.171744;
            ELSIF  (92    <  v_row.gaca_td) AND (v_row.gaca_td <= 99  )                            THEN v_b[4] :=  -0.095316;
            END IF;


            IF    ( v_age <  19 )                                                                  THEN v_b[5] :=  -0.524335;
            ELSIF ( 19 <= v_age ) AND (v_age < 24 )                                                THEN v_b[5] :=  -0.224857;
            ELSIF ( 24 <= v_age ) AND (v_age < 31 )                                                THEN v_b[5] :=  -0.152906;
            ELSIF ( 31 <= v_age ) AND (v_age < 40 )                                                THEN v_b[5] :=  -0.031969;
            ELSIF ( 40 <= v_age ) AND (v_age < 51 )                                                THEN v_b[5] :=   0.071473;
            ELSIF ( 51 <= v_age ) AND (v_age < 60 )                                                THEN v_b[5] :=   0.138119;
            ELSIF ( 60 < v_age  ) AND (v_age < 999)                                                THEN v_b[5] :=   0.855210;
            ELSIF ( v_age = 999 )                                                                  THEN v_b[5] :=  -0.524335;
            END IF;

            IF v_row.g_employ_name IS NULL                           OR
               v_row.g_employ_name = 'NOT EMPLOYED'                  OR
               v_row.g_employ_name = 'NOT-EMPLOYED'                  OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNE  ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNE, ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNEM ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNEMP' OR
               substring(v_row.g_employ_name from 1 for 5) = 'NONE ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'MINOR'                               THEN v_b[6] := -0.861522; 
            END IF;

            IF substring(v_row.g_employ_name from 1 for 8) = 'SELF EMP'                            THEN v_b[7] := -1.898466;
            END IF;

            IF      (v_row.median_hh_income >= 0    )  AND (v_row.median_hh_income <  30705 )      THEN v_b[8] := -0.184940;
            ELSIF   (v_row.median_hh_income >= 30705 ) AND (v_row.median_hh_income <  37972 )      THEN v_b[8] := -0.048060;
            ELSIF   (v_row.median_hh_income >= 37972 ) AND (v_row.median_hh_income <  45159 )      THEN v_b[8] :=  0.024015;
            ELSIF   (v_row.median_hh_income >= 45159 ) AND (v_row.median_hh_income <  999999)      THEN v_b[8] :=  0.168052;
            ELSIF   (v_row.median_hh_income =  999999)                                             THEN v_b[8] :=  0.091894;
            END IF;



            FOR v_j IN 1..8 LOOP
            v_row.commercial_score := v_row.commercial_score + v_b[v_j];
            END LOOP;

            v_row.commercial_score := v_row.commercial_score + -3.74029799;
            v_row.commercial_score := 10000 / ( 1 + exp( (-1)*(v_row.commercial_score) ) );
            v_row.commercial_score := round((v_row.commercial_score::numeric),2);

            IF    v_row.commercial_score < 0.01 THEN v_row.commercial_score := 0.01;
            ELSIF v_row.commercial_score > 999  THEN v_row.commercial_score := 999;
            END IF;

            v_row.tpl_score := v_row.commercial_score;

            IF v_row.threshold_bal_commercial IS NOT NULL AND v_row.threshold_bal_commercial < v_row.balance THEN v_override_commercial := 1;
            END IF;

            IF v_row.threshold_bal_tpl IS NOT NULL AND v_row.threshold_bal_tpl < v_row.balance THEN v_override_tpl := 1;
            END IF;
       
         ELSE

            v_row.commercial_score := random()::numeric(10,6);
            v_row.tpl_score        := v_row.commercial_score;  

         END IF;

    END IF;


     -- SCORING ALGORITHM FOR MEDICAID;

    IF v_row.emdeon_medicaid_flag = '1'  THEN

        v_row.medicaid_score         := 0;
        v_row.scoring_code_medicaid  := 'F';

        IF v_row.processing_type <> 'T'             THEN


            IF    ( v_age <  24 )                                                                  THEN v_b[9] :=   0.424658;
            ELSIF ( 24 <= v_age ) AND (v_age < 29 )                                                THEN v_b[9] :=   0.135225;
            ELSIF ( 29 <= v_age ) AND (v_age < 35 )                                                THEN v_b[9] :=  -0.079043;
            ELSIF ( 35 <= v_age ) AND (v_age < 40 )                                                THEN v_b[9] :=  -0.175141;
            ELSIF ( 40 <= v_age ) AND (v_age < 47 )                                                THEN v_b[9] :=  -0.248322;
            ELSIF ( 47 <= v_age ) AND (v_age < 53 )                                                THEN v_b[9] :=  -0.328600;
            ELSIF ( 53 < v_age  ) AND (v_age < 999)                                                THEN v_b[9] :=  -0.406660;
            ELSIF ( v_age = 999 )                                                                  THEN v_b[9] :=   0.424658;
            END IF;

            IF      (v_row.median_hh_income >= 0    )  AND (v_row.median_hh_income <  30705 )      THEN v_b[10] :=  0.135720;
            ELSIF   (v_row.median_hh_income >= 30705 ) AND (v_row.median_hh_income <  37972 )      THEN v_b[10] := -0.050275;
            ELSIF   (v_row.median_hh_income >= 37972 ) AND (v_row.median_hh_income <  45159 )      THEN v_b[10] := -0.028578;
            ELSIF   (v_row.median_hh_income >= 45159 ) AND (v_row.median_hh_income <  999999)      THEN v_b[10] := -0.049384;
            ELSIF   (v_row.median_hh_income =  999999)                                             THEN v_b[10] := -0.545766;
            END IF;

            IF     (0    <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 1.00)                        THEN v_b[11] :=  -0.119164;
            ELSIF  (1.00 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 2.03)                        THEN v_b[11] :=  -0.050129;
            ELSIF  (2.03 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 3.12)                        THEN v_b[11] :=   0.011051;
            ELSIF  (3.12 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 4.25)                        THEN v_b[11] :=   0.046246;
            ELSIF  (4.25 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m < 6.00)                        THEN v_b[11] :=   0.059070;
            ELSIF  (6.00 <= v_row.gaca_bd24m) AND (v_row.gaca_bd24m <= 92 )                        THEN v_b[11] :=   0.050258;
            ELSIF  (92   <  v_row.gaca_bd24m) AND (v_row.gaca_bd24m <= 99 )                        THEN v_b[11] :=  -0.106911;
            END IF;

            IF     (0      <= v_row.pct_unemployed1) AND (v_row.pct_unemployed1 < 0.0246)          THEN v_b[12] :=  -0.058115;
            ELSIF  (0.0246 <= v_row.pct_unemployed1) AND (v_row.pct_unemployed1 < 0.0320)          THEN v_b[12] :=  -0.029888;
            ELSIF  (0.0320 <= v_row.pct_unemployed1) AND (v_row.pct_unemployed1 < 0.0394)          THEN v_b[12] :=  -0.040435;
            ELSIF  (0.0394 <= v_row.pct_unemployed1) AND (v_row.pct_unemployed1 < 0.0605)          THEN v_b[12] :=   0.067143;
            ELSIF  (0.0605 <= v_row.pct_unemployed1) AND (v_row.pct_unemployed1 < 9.9999)          THEN v_b[12] :=   0.107642;
            ELSIF  (v_row.pct_unemployed1 = 9.9999)                                                THEN v_b[12] :=  -0.477803;
            END IF;

            IF     (0     <= v_row.gaca_td) AND (v_row.gaca_td < 6.30 )                            THEN v_b[13] :=   0.038495;
            ELSIF  (6.30  <= v_row.gaca_td) AND (v_row.gaca_td < 9.63 )                            THEN v_b[13] :=   0.052240;
            ELSIF  (9.63  <= v_row.gaca_td) AND (v_row.gaca_td < 11.47)                            THEN v_b[13] :=   0.019734;
            ELSIF  (11.47 <= v_row.gaca_td) AND (v_row.gaca_td < 14.46)                            THEN v_b[13] :=  -0.003788;
            ELSIF  (14.46 <= v_row.gaca_td) AND (v_row.gaca_td <= 92  )                            THEN v_b[13] :=  -0.056783;
            ELSIF  (92    <  v_row.gaca_td) AND (v_row.gaca_td <= 99  )                            THEN v_b[13] :=  -0.097751;
            END IF;

            IF  v_row.ssn IS NOT NULL                                                              THEN v_b[14] :=  0.934649;
            END IF;

            IF v_row.g_employ_name IS NULL                           OR
               v_row.g_employ_name = 'NOT EMPLOYED'                  OR
               v_row.g_employ_name = 'NOT-EMPLOYED'                  OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNE  ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNE, ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNEM ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'UNEMP' OR
               substring(v_row.g_employ_name from 1 for 5) = 'NONE ' OR
               substring(v_row.g_employ_name from 1 for 5) = 'MINOR'                               THEN v_b[15] :=  0.130547;
            END IF;

            IF substring(v_row.g_employ_name from 1 for 6) = 'RETIRE' OR
               substring(v_row.g_employ_name from 1 for 6) = 'STUDEN' OR
               v_row.g_employ_name  = 'CHILD'                               OR
               v_row.g_employ_name  = 'CHILD /'                             OR
               substring(v_row.g_employ_name from 1 for 6) = 'DISABL' OR
               substring(v_row.g_employ_name from 1 for 6) = 'DISABI' OR
               substring(v_row.g_employ_name from 1 for 7) = 'ON DISA'                             THEN v_b[16] :=  0.589821;
            END IF;

            IF substring(v_row.g_employ_name from 1 for 8) = 'SELF EMP'                            THEN v_b[17] := -0.487764;
            END IF;




            FOR v_j IN 9..17 LOOP
            v_row.medicaid_score := v_row.medicaid_score + v_b[v_j];
            END LOOP;

            v_row.medicaid_score := v_row.medicaid_score + -2.261109309;
            v_row.medicaid_score := 1000 / ( 1 + exp( (-1)*(v_row.medicaid_score) ) );
            v_row.medicaid_score := round((v_row.medicaid_score::numeric),2);

            IF    v_row.medicaid_score < 0.01 THEN v_row.medicaid_score := 0.01;
            ELSIF v_row.medicaid_score > 999  THEN v_row.medicaid_score := 999;
            END IF;

            IF v_row.threshold_bal_medicaid IS NOT NULL AND v_row.threshold_bal_medicaid < v_row.balance THEN v_override_medicaid := 1;
            END IF;

        ELSE

            v_row.medicaid_score := random()::numeric(10,6);

        END IF;

    END IF;


    IF v_row.emdeon_medicare_flag = '1'  THEN

        v_row.medicare_score         := 0;
        v_row.scoring_code_medicare  := 'F';

        IF v_row.processing_type <> 'T'             THEN

            IF v_row.balance >= 99999999  THEN v_row.balance := 99999999;
            END IF;

            v_row.medicare_score := (v_row.balance / 10000)::numeric(10,6);

            IF    v_row.threshold_bal_medicare IS NOT NULL AND v_row.threshold_bal_medicare < v_row.balance THEN v_override_medicare := 1;
            ELSIF substring(v_row.g_employ_name from 1 for 4)  = 'DISA'       OR
                  substring(v_row.g_employ_name from 1 for 4)  = 'DISB'       OR
                  v_row.g_employ_name = 'DIS'                                 OR
                  substring(v_row.g_employ_name from 1 for 6)  = 'ON DIS'                                   THEN v_override_medicare := 1;
            ELSIF v_age >= 65 AND v_age < 999                                                               THEN v_override_medicare := 1;
            END IF;

        ELSE

            v_row.medicare_score := random()::numeric(10,6);

        END IF;

    END IF;

    -- END OF STEP 4.4 SCORING CALCULATION 




    ------------------------------------------------
    -- STEP 4.6 INSERT INTO score_temp_insurance1_t WITH THE SCORE FIELDS

    INSERT INTO score_temp_insurance1_t
    (
     acctnum,
     hid,    
     commercial_score,
     scoring_code_commercial,
     tpl_score,
     scoring_code_tpl,
     medicaid_score,
     scoring_code_medicaid,
     medicare_score,
     scoring_code_medicare,
     emdeon_tpl_flag,
     emdeon_commercial_flag,
     emdeon_medicare_flag,
     emdeon_medicaid_flag,
     count_commercial_max,
     count_tpl_max,
     count_medicaid_max,
     count_medicare_max,
     calc_group,
     override_commercial,
     override_tpl,
     override_medicaid,
     override_medicare
    )
    VALUES
    (
     v_row.acctnum,
     v_row.hid,    
     v_row.commercial_score,
     v_row.scoring_code_commercial,
     v_row.tpl_score,
     v_row.scoring_code_tpl,
     v_row.medicaid_score,
     v_row.scoring_code_medicaid,
     v_row.medicare_score,
     v_row.scoring_code_medicare,
     v_row.emdeon_tpl_flag,
     v_row.emdeon_commercial_flag,
     v_row.emdeon_medicare_flag,
     v_row.emdeon_medicaid_flag,
     v_row.count_commercial_max,
     v_row.count_tpl_max,
     v_row.count_medicaid_max,
     v_row.count_medicare_max,
     v_row.calc_group,
     v_override_commercial,
     v_override_tpl,
     v_override_medicaid,
     v_override_medicare
    ) 
    ;

   ------------------------------------------------
   -- STEP 4.7 THIS IS FOR AUDITING PURPOSE ONLY
   -- CAN BE COMMENTED OUT AFTER AUDIT IS COMPLETE
   INSERT INTO score_vb_insurance1_t
   (
  hid,
  acctnum,
  v_b1,
  v_b2,
  v_b3,
  v_b4,
  v_b5,
  v_b6,
  v_b7,
  v_b8,
  v_b9,
  v_b10,
  v_b11,
  v_b12,
  v_b13,
  v_b14,
  v_b15,
  v_b16,
  v_b17
   )
   VALUES
   (
  v_row.hid,
  v_row.acctnum,
  v_b[1],
  v_b[2],
  v_b[3],
  v_b[4],
  v_b[5],
  v_b[6],
  v_b[7],
  v_b[8],
  v_b[9],
  v_b[10],
  v_b[11],
  v_b[12],
  v_b[13],
  v_b[14],
  v_b[15],
  v_b[16],
  v_b[17]
   );

END LOOP;


    ------------------------------------------------
    -- STEP 5 LONG STEP TO APPLY ALL THE THRESHOLDS AND OVERRIDE

  RAISE NOTICE 'START TO APPLY THRESHOLD AND POPULATE TEMPORARY TABLES';

  IF v_tot_commercial_count > 0  THEN

  	OPEN v_scursa FOR SELECT
       		hid,
       		acctnum,
                scoring_code_commercial,
                count_commercial_max,              
                calc_group
    	FROM
       		score_temp_insurance1_t
    	WHERE
       		emdeon_commercial_flag = '1' 
    	ORDER BY
      		calc_group,commercial_score DESC,acctnum
    	;

  	v_commercial_count     := 1;

  	LOOP
    		FETCH v_scursa INTO v_srec;
    		IF NOT FOUND
    		THEN
      		EXIT;
    		END IF;
   
                IF v_commercial_count = 1 THEN v_group := v_srec.calc_group;
                END IF;

                IF v_srec.calc_group <> v_group THEN
                    v_group            := v_srec.calc_group;
                    v_commercial_count := 1;
                END IF;
 
    		IF v_commercial_count <= v_srec.count_commercial_max THEN v_srec.scoring_code_commercial := 'P1';
                ELSE                                                      v_srec.scoring_code_commercial := 'F';
    		END IF;

    		v_commercial_count := v_commercial_count + 1;
    
 
    		INSERT INTO temp_commercial
    		(
     		hid,
     		acctnum,
     		scoring_code_commercial
    		)
     		VALUES
    		(
     		v_srec.hid,
     		v_srec.acctnum,
     		v_srec.scoring_code_commercial
    		)
    		;
  	END LOOP;   

  END IF;

  --

  IF v_tot_tpl_count > 0  THEN

        OPEN v_scursb FOR SELECT
                hid,
                acctnum,
                scoring_code_tpl,
                count_tpl_max,
                calc_group
        FROM
                score_temp_insurance1_t
        WHERE
                emdeon_tpl_flag = '1'
        ORDER BY
                calc_group,commercial_score DESC,acctnum
        ;

        v_tpl_count     := 1;

        LOOP
                FETCH v_scursb INTO v_srec;
                IF NOT FOUND
                THEN
                EXIT;
                END IF;

                IF v_tpl_count = 1 THEN v_group := v_srec.calc_group;
                END IF;

                IF v_srec.calc_group <> v_group THEN
                    v_group            := v_srec.calc_group;
                    v_tpl_count        := 1;
                END IF;

                IF v_tpl_count <= v_srec.count_tpl_max THEN v_srec.scoring_code_tpl := 'P1';
                ELSE                                        v_srec.scoring_code_tpl := 'F';
                END IF;

                v_tpl_count := v_tpl_count + 1;


                INSERT INTO temp_tpl
                (
                hid,
                acctnum,
                scoring_code_tpl
                )
                VALUES
                (
                v_srec.hid,
                v_srec.acctnum,
                v_srec.scoring_code_tpl
                )
                ;
        END LOOP;

  END IF;

--

  IF v_tot_medicaid_count > 0  THEN

        OPEN v_scursc FOR SELECT
                hid,
                acctnum,
                scoring_code_medicaid,
                count_medicaid_max,
                calc_group
        FROM
                score_temp_insurance1_t
        WHERE
                emdeon_medicaid_flag = '1'
        ORDER BY
                calc_group,medicaid_score DESC,acctnum
        ;

        v_medicaid_count     := 1;

        LOOP
                FETCH v_scursc INTO v_srec;
                IF NOT FOUND
                THEN
                EXIT;
                END IF;

                IF v_medicaid_count = 1 THEN v_group := v_srec.calc_group;
                END IF;

                IF v_srec.calc_group <> v_group THEN
                    v_group            := v_srec.calc_group;
                    v_medicaid_count   := 1;
                END IF;

                IF v_medicaid_count <= v_srec.count_medicaid_max THEN v_srec.scoring_code_medicaid := 'P1';
                ELSE                                                  v_srec.scoring_code_medicaid := 'F';
                END IF;

                v_medicaid_count := v_medicaid_count + 1;


                INSERT INTO temp_medicaid
                (
                hid,
                acctnum,
                scoring_code_medicaid
                )
                VALUES
                (
                v_srec.hid,
                v_srec.acctnum,
                v_srec.scoring_code_medicaid
                )
                ;
        END LOOP;

  END IF;

--

  IF v_tot_medicare_count > 0  THEN

        OPEN v_scursd FOR SELECT
                hid,
                acctnum,
                scoring_code_medicare,
                count_medicare_max,
                calc_group
        FROM
                score_temp_insurance1_t
        WHERE
                emdeon_medicare_flag = '1'
        ORDER BY
                calc_group,medicare_score DESC,acctnum
        ;

        v_medicare_count     := 1;

        LOOP
                FETCH v_scursd INTO v_srec;
                IF NOT FOUND
                THEN
                EXIT;
                END IF;

                IF v_medicare_count = 1 THEN v_group := v_srec.calc_group;
                END IF;

                IF v_srec.calc_group <> v_group THEN
                    v_group            := v_srec.calc_group;
                    v_medicare_count   := 1;
                END IF;

                IF v_medicare_count <= v_srec.count_medicare_max THEN v_srec.scoring_code_medicare := 'P1';
                ELSE                                                  v_srec.scoring_code_medicare := 'F';
                END IF;

                v_medicare_count := v_medicare_count + 1;


                INSERT INTO temp_medicare
                (
                hid,
                acctnum,
                scoring_code_medicare
                )
                VALUES
                (
                v_srec.hid,
                v_srec.acctnum,
                v_srec.scoring_code_medicare
                )
                ;
        END LOOP;

  END IF;

  RAISE NOTICE 'FINISH TO APPLY THRESHOLD AND POPULATE TEMPORARY TABLES';


    ------------------------------------------------
    -- STEP 6 FINALLY WE UPDATE THE score_temp_insurance1_t TABLE


  RAISE NOTICE 'START FINAL UPDATE OF score_temp_insurance1_t';

  IF v_tot_commercial_count > 0  THEN

     UPDATE score_temp_insurance1_t
     SET
       scoring_code_commercial = (CASE WHEN override_commercial = 1 AND ba.scoring_code_commercial = 'F' THEN 'P2' 
                                       ELSE                                                                   ba.scoring_code_commercial
                                       END),
       emdeon_commercial_flag  = (CASE WHEN override_commercial = 1 OR ba.scoring_code_commercial = 'P1' THEN '1'
                                       ELSE                                                                   'J'
                                       END)
     FROM
        temp_commercial ba
     WHERE
       (ba.hid = score_temp_insurance1_t.hid AND ba.acctnum = score_temp_insurance1_t.acctnum);

  END IF;

  --

  IF v_tot_tpl_count > 0  THEN

     UPDATE score_temp_insurance1_t
     SET
       scoring_code_tpl = (CASE WHEN override_tpl = 1 AND ba.scoring_code_tpl = 'F' THEN 'P2'
                                ELSE                                                      ba.scoring_code_tpl
                                END),
       emdeon_tpl_flag  = (CASE WHEN override_tpl = 1 OR ba.scoring_code_tpl = 'P1' THEN '1'
                                ELSE                                                     'J'
                                END)
     FROM
        temp_tpl ba
     WHERE
       (ba.hid = score_temp_insurance1_t.hid AND ba.acctnum = score_temp_insurance1_t.acctnum);

  END IF;

  --

  IF v_tot_medicaid_count > 0  THEN

     UPDATE score_temp_insurance1_t
     SET
       scoring_code_medicaid = (CASE WHEN override_medicaid = 1 AND ba.scoring_code_medicaid = 'F' THEN 'P2'
                                     ELSE                                                               ba.scoring_code_medicaid
                                     END),
       emdeon_medicaid_flag  = (CASE WHEN override_medicaid = 1 OR ba.scoring_code_medicaid = 'P1' THEN '1'
                                     ELSE                                                               'J'
                                     END)
     FROM
        temp_medicaid ba
     WHERE
       (ba.hid = score_temp_insurance1_t.hid AND ba.acctnum = score_temp_insurance1_t.acctnum);

  END IF;

  --

  IF v_tot_medicare_count > 0  THEN

     UPDATE score_temp_insurance1_t
     SET
       scoring_code_medicare = (CASE WHEN override_medicare = 1 AND ba.scoring_code_medicare = 'F' THEN 'P2'
                                     ELSE                                                                ba.scoring_code_medicare
                                     END),
       emdeon_medicare_flag  = (CASE WHEN override_medicare = 1 OR ba.scoring_code_medicare = 'P1' THEN '1'
                                     ELSE                                                               'J'
                                     END)
     FROM
        temp_medicare ba
     WHERE
       (ba.hid = score_temp_insurance1_t.hid AND ba.acctnum = score_temp_insurance1_t.acctnum);

  END IF;


  RAISE NOTICE 'FINISH FINAL UPDATE OF score_temp_insurance1_t';
 



RAISE NOTICE 'FINISH SCORE CALCULATION AND INSERT INTO score_temp_insurance1_t';
PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,4,'FINISH SCORE CALCULATION AND INSERT INTO score_temp_insurance1_t');

  RETURN v_status;

END
$$ LANGUAGE plpgsql;
